package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import orm.ChildrenBehaviorPurpose;
import orm.CollectivityMonthWeekAim;
import orm.CollectivityWeekTimeBucket;
import util.DBHelper;

public class CMonthWeekAimDAO {
	
	public void insertCMonthWeekAim(Statement statement, 
			ArrayList<CollectivityMonthWeekAim> cmwaArr, String cMonthArrangeID) throws SQLException {
		CollectivityMonthWeekAim cmwa = null;
		for (int i = 0; i < cmwaArr.size(); i++) {
			cmwa = cmwaArr.get(i);
			statement.execute("insert into collectivitymonthweekaim values('" + cMonthArrangeID + "'," +
					cmwa.getWeekID() + ", " + cmwa.getWeekAimID() + ", '" + cmwa.getWeekAimDescribe() +
					"', '" + cmwa.getCWeekArrangeID() + "')");
		}
	}
	
	public void updateCMonthWeekAim(Statement statement,
			ArrayList<CollectivityMonthWeekAim> cmwaArr, String cMonthArrangeID) throws SQLException {
		CollectivityMonthWeekAim cmwa = null;
		for (int i = 0; i < cmwaArr.size(); i++) {
			cmwa = cmwaArr.get(i);
			StringBuffer sqlStr = new StringBuffer();
			sqlStr.append("update collectivitymonthweekaim set WeekAimDescribe = '");
			sqlStr.append(cmwa.getWeekAimDescribe() + "', CWeekArrangeID = '" + cmwa.getCWeekArrangeID());
			sqlStr.append("' where cMonthArrangeID = '");
			sqlStr.append(cmwa.getCMonthArrangeID() + "' and WeekID = " + cmwa.getWeekID() + " and WeekAimID = " + cmwa.getWeekAimID() + "");
			
			statement.execute(sqlStr.toString());
		}
	}
	
	public void deleteCMonthWeekAim(Statement statement, String cMonthArrangeID) throws SQLException {
		statement.execute("delete from collectivitymonthweekaim where CMonthArrangeID='" + cMonthArrangeID + "'");
	}
	
	public ArrayList<CollectivityMonthWeekAim> getcmwaByCWAID(String cMonthArrangeID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList<CollectivityMonthWeekAim> al = new ArrayList<CollectivityMonthWeekAim>();
		CollectivityMonthWeekAim cmwa = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from collectivitymonthweekaim where CMonthArrangeID='" + cMonthArrangeID + "'");
		
		while (rs.next()) {
			cmwa = new CollectivityMonthWeekAim();
			int i = 1;
			cmwa.setCMonthArrangeID(rs.getString(i++));
			cmwa.setWeekID(rs.getInt(i++));
			cmwa.setWeekAimID(rs.getInt(i++));
			cmwa.setWeekAimDescribe(rs.getString(i++));
			cmwa.setCWeekArrangeID(rs.getString(i++));
			al.add(cmwa);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	public ArrayList<String> getTPIDSSByCMonthArrangeID(String cMonthArrangeID, int weekID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select CWeekArrangeID from collectivitymonthweekaim where CMonthArrangeID = '" + cMonthArrangeID + "' and WeekID = " + weekID + " and CWeekArrangeID<>''");
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		ArrayList<String> al = new ArrayList<String>();
	
		while (rs.next()) {
			al.add(rs.getString(1));
		}
		rs.close();
		stmt.close();
		conn.close();
		
		ArrayList<String> tpids = new ArrayList<String>();
		for(String s:al){
			if(s.indexOf(",")==-1){
				tpids.add(s);
			}else{
				for(String temp : s.split(",")){
					tpids.add(temp);
				}
			}
		}
		
		return tpids;
	}
}
